How to Calculate Cumulative Sum-Running Total

SQL Tutorial Home

Let’s say we want to see a report with cumulative values, for example the cumulative daily revenue at different timestamps. We want the cumulative revenue at each timestamp in the table:

WITH sales_table AS (
  SELECT 120 as amount, DATETIME '2022-01-03 12:34:56' AS sales_ts UNION ALL
  SELECT 100, DATETIME '2022-01-01 11:21:04' UNION ALL
  SELECT 100, DATETIME '2022-01-01 12:11:36' UNION ALL
  SELECT 90,  DATETIME '2022-01-01 12:15:14' UNION ALL
  SELECT 220, DATETIME '2022-01-01 13:31:42' 
)
SELECT sales_ts, amount from sales_table
sales_ts amount
2022-01-01T10:34:56 120
2022-01-01T11:21:04 220
2022-01-01T12:11:36 320
2022-01-01T12:15:14 410
2022-01-01T13:31:42 630 
WITH sales_table AS (
  SELECT 120 as amount, DATETIME '2022-01-01 10:34:56' AS sales_ts UNION ALL
  SELECT 100, DATETIME '2022-01-01 11:21:04' UNION ALL
  SELECT 100, DATETIME '2022-01-01 12:11:36' UNION ALL
  SELECT 90,  DATETIME '2022-01-01 12:15:14' UNION ALL
  SELECT 220, DATETIME '2022-01-01 13:31:42' 
)
select  	sales_ts,
sum(amount) over (order by sales_ts) 
From        sales_table

The previous query calculates the cumulative revenue considering only the records with timestamps previous to the current timestamp.

sales_ts cumulative
2022-01-01T10:34:56 120
2022-01-01T11:21:04 220
2022-01-01T12:11:36 320
2022-01-01T12:15:14 410
2022-01-01T13:31:42 630

IN THIS PAGE